# coding=utf-8

import codecs
import os

#from p3_create import get_tab_definition, get_tab_modification

#    print(get_tab_definition(conn, "SCOTT", "EMP"))
#    print(get_tab_modification(conn, "SCOTT", "EMP"))
from p3_export import export_tab_data, create_import_sql

import cx_Oracle
import datetime
import time

USERNAME = 'chenchang313'
PASSWORD = 'Orange2014'
DATABASE = '10.33.21.166:1530/pif'

SQL = """select owner, table_name, num_rows
  from dba_tables
 where owner not in
       ('AURORA$JIS$UTILITY$', 'CTXSYS', 'DBAQRY', 'DBCONN', 'DBMGR',
        'DBMON', 'DBMONOPR', 'DBQUA', 'DBSNMP', 'DBSTATS', 'DEPLOYOP', 'DIP',
        'DMLBAK', 'EXFSYS', 'FGLPA', 'FOGLIGHT', 'HR', 'I3_ORCL', 'LBACSYS',
        'MDSYS', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OSE$HTTP$ADMIN',
        'OUTLN', 'OVSEE', 'PERFSTAT', 'PUB_QUERY', 'PUB_TEST', 'QS',
        'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS',
        'QUEST', 'SCOTT', 'SPOTLIGHT', 'SQLTXPLAIN', 'SYS', 'SYSTEM',
        'SYSMAN', 'TOAD', 'TOM', 'TRACESVR', 'TSMSYS', 'VERITAS_I3_ORCL',
        'WKSYS', 'WKSYS', 'WMSYS', 'XDB', 'ORACLE_OCM', 'APPMGR',
        'I3_ORCL_R1')
   and table_name not like 'BIN$%'
 order by num_rows"""

SQL = """select owner, table_name, num_rows
  from dba_tables
 where owner = 'SCOTT' and table_name='TX'
 order by num_rows"""

def get_table_list():
    result = (
        ('PIFDATA', 'PIF_QRTZ_JOB_DETAILS'),
        ('PIFDATA', 'PIF_QRTZ_TRIGGERS'),
        ('PIFDATA', 'PIF_QRTZ_CRON_TRIGGERS'),
        ('PIFDATA', 'EMAIL_SWITCH'),
        ('PIFDATA', 'PIF_PRINT_TEMPLET'),
        ('PIFDATA', 'PIF_QRTZ_SCHEDULER_STATE'),
        ('PIFDATA', 'PCIS_EMAIL_GROUP'),
        ('PIFDATA', 'HIS_PROD_PROD_CODE'),
        ('PIFDATA', 'PIF_QRTZ_LOCKS'),
        ('PIFDATA', 'PCIS_CLAUSE_PERMISSION'),
        ('PIFDATA', 'PCIS_CREATIVE'),
        ('PIFDATA', 'JOB_SCHEDULE_TABLE'),
        ('PIFDATA', 'PCIS_EMAIL_RECEIVER'),
        ('PIFDATA', 'PROD_ITEM_TYPE'),
        ('PIFDATA', 'ROLE_EXPERT_TYPE_RELATION'),
        ('PIFDATA', 'JOB_ERROR_LOG'),
        ('PIFDATA', 'PCIS_TERM_MIDDLE_PROCESS'),
        ('PIFDATA', 'PCIS_LEARN'),
        ('PIFDATA', 'PCIS_TERM_RELA_PROCESS'),
        ('PIFDATA', 'PIF_EMAIL_INFO'),
        ('PIFDATA', 'PCIS_RHAP_RECORD'),
        ('PIFDATA', 'ROLE_OPERATE_RELATION'),
        ('PIFDATA', 'PCIS_NEW_TERM'),
        ('PIFDATA', 'MUST_ADDRESSEE'),
        ('PIFDATA', 'TERM_TERM_RELATION'),
        ('PIFDATA', 'PIF_CONFIGS'),
        ('PIFDATA', 'HIS_PCIS_EMAIL_RECEIVER'),
        ('PIFDATA', 'PIF_EMAIL_DATE'),
        ('PIFLOGTMP', 'SERVICE_AUTOREPORT_CONTENT'),
        ('PIFDATA', 'PRINT_TEMPLET_INFO'),
        ('PIFDATA', 'DMLBK20100915000000012'),
        ('PIFDATA', 'DMLBK20100915000000008'),
        ('PIFDATA', 'HIS_TERM_TERM_RELATION'),
        ('PIFDATA', 'DMLBK20100915000000011'),
        ('PIFDATA', 'DMLBK20100915000000007'),
        ('PIFDATA', 'PCIS_QUESTION_ITEM'),
        ('PIFDATA', 'DMLBK20100915000000006'),
        ('PIFDATA', 'EXP_PROD_CAT'),
        ('PIFDATA', 'PIF_PROP_GEN_RULE'),
        ('PIFDATA', 'PCIS_NEW_TERM_PROCESS'),
        ('PIFDATA', 'DMLBK20100915000000010'),
        ('PIFDATA', 'PCIS_QUESTION_TERMS'),
        ('PIFDATA', 'PROD_SORT'),
        ('PIFDATA', 'PCIS_DOWNLOAD_APPLY'),
        ('PIFDATA', 'PCIS_QUESTION_CATALOGUE'),
        ('PIFDATA', 'EXP_INFO_COMM'),
        ('PIFDATA', 'EXP_SERVICE'),
        ('PIFDATA', 'EXP_SER_REG_SER'),
        ('PIFDATA', 'DMLBK20100915000000013'),
        ('PIFDATA', 'EXP_ROLE_BRANCH'),
        ('PIFDATA', 'CAT_CATEGORY'),
        ('PIFDATA', 'PCIS_QUESTION_ALLOCATE'),
        ('PIFDATA', 'CAT_CATEGORY_RELATION'),
        ('PIFDATA', 'DMLBK20100915000000016'),
        ('PIFDATA', 'HIS_PROD_PROD_CHNL'),
        ('PIFDATA', 'PCIS_QUESTION_ANSWER'),
        ('PIFDATA', 'DMLBK20100915000000004'),
        ('PIFDATA', 'DMLBK20100915000000005'),
        ('PIFDATA', 'DMLBK20100915000000001'),
        ('PIFDATA', 'PCIS_PERMISSION'),
        ('PIFLOGTMP', 'SYNC_BANK_NC_PROD_INFO'),
        ('PIFDATA', 'PROD_PROD_SALES'),
        ('PIFDATA', 'PROD_PROD_CUST'),
        ('PIFDATA', 'DMLBK20100915000000003'),
        ('PIFDATA', 'PIF_PROPDESC_TBL'),
        ('PIFDATA', 'CHAPTER_CATEGORY_RELATION'),
        ('PIFDATA', 'DMLBK20100915000000014'),
        ('PIFDATA', 'CHAPTER'),
        ('PIFDATA', 'PROD_PROD_CHNL'),
        ('PIFDATA', 'CHAPTER_RELATION'),
        ('PIFDATA', 'PROD_PROD_CODE'),
        ('PIFDATA', 'HIS_PROD_PROD_SALES'),
        ('PIFDATA', 'HIS_PROD_PROD_CUST'),
        ('PIFDATA', 'HIS_PROD_INFO'),
        ('PIFDATA', 'CONTENT'),
        ('PIFDATA', 'PROD_INFO'),
        ('PIFDATA', 'DMLBK20100915000000002'),
        ('PIFLOGTMP', 'SYNC_BM_PROP_INFO_TMP'),
        ('PIFLOGTMP', 'SYNC_ANNUITY_PROD_INFO'),
        ('PIFDATA', 'DMLBK20100915000000009'),
        ('PIFDATA', 'HIS_CAT_PROD_CATEGORY'),
        ('PIFDATA', 'TERMS'),
        ('PIFDATA', 'PCIS_TERMS_TRENDS'),
        ('PIFDATA', 'TERM_CATEGORY_RELATION'),
        ('PIFDATA', 'CAT_PROD_CATEGORY'),
        ('PIFDATA', 'PIF_PROPCTRL_TBL'),
        ('PIFDATA', 'HIS_TERM_CATEGORY_RELATION'),
        ('PIFDATA', 'HIS_TERMS'),
        ('PIFDATA', 'JOB_RUNNING_LOG'),
        ('PIFDATA', 'PROD_ITEM_PROD'),
        ('PIFDATA', 'PROD_PROD_PERSON'),
        ('PIFDATA', 'PCIS_ETDL_RECORD'),
        ('PIFDATA', 'DMLBK20100915000000017'),
        ('PIFDATA', 'HIS_PROD_PROD_PERSON'),
        ('PIFDATA', 'HIS_PROD_ITEM_PROD'),
        ('PIFDATA', 'HIS_PROD_BANK_PROP'),
        ('PIFDATA', 'LOGIN_RECORD'),
        ('PIFDATA', 'PROD_BANK_PROP'),
        ('PIFDATA', 'PS_PAIC_ORG_CHG_ST'),
        ('PIFDATA', 'PS_PAIC_EMP_INFO'),
        ('PIFDATA', 'HIS_TERMS_CHAPTER_RELATION'),
        ('PIFLOGTMP', 'ANNUITY_PIS_SCHEME'),
        ('PIFDATA', 'PCIS_TERM_DISCUSS'),
        ('PIFDATA', 'PROD_INSURANCE_PROP'),
        ('PIFDATA', 'PROD_SALE_MEMO'),
        ('PIFDATA', 'PROD_PROD_COMMISSION'),
        ('PIFDATA', 'PROD_SEVICE'),
        ('PIFDATA', 'PROD_REALITION'),
        ('PIFDATA', 'PROD_AUDIT'),
        ('PIFDATA', 'PROD_SELL_POINT_PROD'),
        ('PIFDATA', 'CAT_CATEGORY_ATTRIBUTE'),
        ('PIFDATA', 'DMLBK20100915000000015'),
        ('PIFDATA', 'HIS_PROD_INSURANCE_PROP'),
        ('PIFDATA', 'PIF_QRTZ_JOB_LISTENERS'),
        ('PIFDATA', 'PIF_QRTZ_SIMPLE_TRIGGERS'),
        ('PIFDATA', 'PIF_QRTZ_BLOB_TRIGGERS'),
        ('PIFDATA', 'PIF_QRTZ_TRIGGER_LISTENERS'),
        ('PIFDATA', 'PIF_QRTZ_CALENDARS'),
        ('PIFDATA', 'PIF_QRTZ_PAUSED_TRIGGER_GRPS'),
        ('PIFDATA', 'PIF_QRTZ_FIRED_TRIGGERS'),
        ('PIFDATA', 'PROD_TERMS'),
        ('PIFDATA', 'HIS_PROD_TERMS'),
        ('PIFDATA', 'TERMS_CHAPTER_RELATION'),
        ('PIFDATA', 'PCIS_PPTX_LEARN'),
        ('PIFDATA', 'PCIS_LEARN_TERMS_REL'),
        ('PIFDATA', 'PCIS_TERM_CREATIVE_REL'),
        ('PIFDATA', 'PCIS_NTERM_TERM_REL'),
        ('PIFDATA', 'PCIS_NEW_TERM_ITEM'),
        ('PIFDATA', 'PCIS_QA_CATALOGUE'),
        ('PIFTRG', 'PIF_TR_SWITCH'),
        ('PIFTRG', 'PIF_TR_ERROR_LOG'),
        ('PIFLOGTMP', 'PIF_PROD_SYNC_ERROR_LOG'),
        ('PIFLOGTMP', 'ANNUITY_PTS_SCHEME'),
        ('PIFLOGTMP', 'ANNUITY_PAS_SCHEME'),
        ('PIFDATA', 'HIS_ROW_ID_TMP'),
    )
    return result


def my_error_hanlder(f):
    def _error_handler(msg):
        f.write(msg)
        f.write('\n')
    return _error_handler

def test_db():
    os.environ["NLS_LANG"] = "SIMPLIFIED CHINESE_CHINA.AL32UTF8"
    con = cx_Oracle.connect(USERNAME, PASSWORD, DATABASE)
    cursor = con.cursor()
    cursor.execute(SQL)
    with codecs.open('pif_tables.sql', 'w', "utf-8") as ftab:
        with codecs.open('pif_indexes.sql', 'w', encoding="utf-8") as fidx:
            with codecs.open('error.txt', 'w', encoding="utf-8") as ferr:
                for (owner, table_name, num_rows) in cursor:
                    print(owner + " " + table_name)
                    tab_def = get_tab_definition(con, owner, table_name, my_error_hanlder(ferr))
#                    print(tab_def)
                    ftab.write(tab_def)
                    tab_mod = get_tab_modification(con, owner, table_name)
                    fidx.write(tab_mod)

#        print (r)
    cursor.close()
    con.close()

def test_db2():
    os.environ["NLS_LANG"] = "SIMPLIFIED CHINESE_CHINA.AL32UTF8"
    con = cx_Oracle.connect(USERNAME, PASSWORD, DATABASE)
    cursor = con.cursor()
    cursor.execute(SQL)
    for (owner, table_name, num_rows) in cursor:
        export_tab_data(con, owner, table_name, "%s.%s.txt" % (owner, table_name))
        create_import_sql(con, owner, table_name, "%s.%s.sql" % (owner, table_name))
#        print(owner + " " + table_name)

#            tab_def = get_tab_definition(con, owner, table_name, my_error_hanlder(ferr))
#                    print(tab_def)
#            ftab.write(tab_def)
#            tab_mod = get_tab_modification(con, owner, table_name)
#            fidx.write(tab_mod)

#        print (r)
    cursor.close()
    con.close()

def test_db3():
    rs = get_table_list()
    s, e = 10, 30
    os.environ["NLS_LANG"] = "SIMPLIFIED CHINESE_CHINA.AL32UTF8"
    con = cx_Oracle.connect(USERNAME, PASSWORD, DATABASE)
    for (owner, table_name) in rs[s:e]:
        export_tab_data(con, owner, table_name, "%s.%s.txt" % (owner, table_name))
        create_import_sql(con, owner, table_name, "%s.%s.sql" % (owner, table_name))
    con.close()

def test_db4():
    rs = get_table_list()
    s, e = 90, 100
    with open('import.%d-%d.txt' % (s, e), 'w') as f:
        for (owner, table_name) in rs[s:e]:
            print("mysql -u deployop -ppaic1234 -h 10.25.29.147 -P 5000 -D pif --default-character-set=utf8 < %s.%s.sql" % (owner, table_name))
            #f.write("mysql -u deployop -ppaic1234 -h 10.25.29.147 -P 5000 -D pif --default-character-set=utf8 < %s.%s.sql\r\n" % (owner, table_name))
            f.write("\. %s.%s.sql\r\n" % (owner, table_name))

def test():
    print ('testa')
    test_db4()

if __name__ == "__main__":
    test()
